Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


How the Oracle Index Works

When an index is created, an index segment is automatically allocated. This index segment contains information that speeds access to data by determining the location of indexed data with as few I/Os as possible. Oracle indexes data by using an index structure known as a B*-Tree index.

A B*-Tree index is designed to balance the access time to any row. A B*-Tree index is a tree of descending comparison values (see Figure 10.6). As you traverse down the index, you compare the desired value with the values in the upper-level index blocks called branch blocks. Based on the outcome of the comparison with the branch blocks, you compare the desired value with more branch blocks until you reach the lowest-level index blocks. The index blocks on the lowest level, called leaf blocks, contain every indexed data value and the associated ROWID of that data.


Figure 10.6  The B*-Tree index structure.

With a unique index, there is one ROWID per data value in the leaf block (see Figure 10.7). With a nonunique index, there may be several values associated with the data value. In the case of the nonunique index, the data values are sorted first by the index key and then by the ROWID.


Figure 10.7  The index block structure.

With a B*-Tree index, all the leaf blocks are at the same level. Access of index data takes approximately the same time regardless of the value of the data. B*-Tree indexes provide quick access to data whether it is an exact match or a range query. In addition, B*-Tree indexes provides good performance regardless of the size of the table—and the performance does not degrade as the table grows.

What To Index

An index is effective only when it is used. The use of the index is mostly determined by the column values that are indexed. Remember that the more indexes you have on a table, the more overhead is incurred during updates, inserts, and deletes. Therefore, it is important to index selectively.

Use the following guidelines for deciding which tables to index:

  Index tables when queries select only a small number of rows. Queries that select a large number of rows defeat the purpose of the index. Use indexes when queries access less than 5 percent of the rows in the table.
  Don’t index tables that are frequently updated. Updates, inserts, and deletes incur extra overhead when indexed. Base your decision to index on the number of updates, inserts, and deletes relative to the number of queries to the table.
  Index tables that don’t have duplicate values on the columns usually selected in WHERE clauses. Tables in which the selection is based on TRUE or FALSE values are not good candidates for indexing
  Index tables that are queried with relatively simple WHERE clauses. Complex WHERE clauses may not take advantage of indexes.

If you decide to use an index, it is important to decide the columns on which you put the index. Depending on the table, you may choose to index one or more columns.

Use the following guidelines for deciding which columns to index:

  Choose columns that are most frequently specified in WHERE clauses. Frequently accessed columns can most benefit from indexes.
  Don’t index columns that do not have many unique values. Columns in which a good percentage of rows are duplicates cannot take advantage of indexing.
  Columns that have unique values are excellent candidates for indexing. Oracle automatically indexes columns that are unique or primary keys defined with constraints. These columns are most effectively optimized by indexes.
  Columns that are commonly used to join tables are good candidates for indexing.
  Frequently modified columns probably should not be index columns because of the overhead involved in updating the index.

In certain situations, the use of composite indexes may be more effective than individual indexes. Here are some examples of where composite indexes may be quite useful:

  When two columns are not unique individually but are unique together, composite indexes may work very well. For example, although columns A and B have few unique values, rows with a particular combination of columns A AND B are mostly unique. Look for WHERE clauses with AND operators.
  If all values of a SELECT statement are in a composite index, Oracle does not query the table; the result is returned from the index.
  If several different queries select the same rows with different WHERE clauses based on different columns, consider creating a composite index with all the columns used in the WHERE statements.

Composite indexes can be quite useful when they are carefully designed. As with single-column indexes, they are most effective if applications are written with the indexes in mind.

Once you have created the index, you should periodically use the SQL Trace facility to determine whether your queries are taking advantage of the indexes. It may be worth the effort to try the query with and without indexes and then compare the results to see whether the index is worth the space it uses.

In summary, indexes can significantly improve performance in your system if they are used properly. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Once you decide to use an index, you must decide which columns to index.

Indexing an inappropriate column or table can actually reduce performance. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times.

Careful planning and periodic testing with the SQL Trace feature can lead to a very effective use of indexes, with optimal performance being the outcome.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.